#!/usr/bin/env python
#encoding:UTF-8
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import func
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://tdh:YbslTdh1@rds77jvjmmnaurb.mysql.rds.aliyuncs.com:3306/picbook'
db = SQLAlchemy(app)

class Book(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(64),unique=True)
    cover = db.Column(db.String(128))
    pagenum = db.Column(db.Integer)
    status = db.Column(db.Integer)
    desc = db.Column(db.String(128))
    seriesid = db.Column(db.Integer,db.ForeignKey('series.id'))
    pics = db.relationship('Pic',backref="book", lazy='dynamic')


    def __init__(self,name,cover,status):
        self.name = name
        self.cover = cover
        self.status = status



    def update(self,name,desc,seriesid,status):
        try:
            self.name = name
            self.desc = desc
            if seriesid!=0:
                self.seriesid = int(seriesid)
            self.status = int(status)
            db.session.commit()
            return self.id
        except Exception,e:
            db.session.rollback()
            return e
        finally:
            return 0

    def add(self):
        try:
            db.session.add(self)
            db.session.commit()
            return self.id
        except Exception,e:
            db.session.rollback()
            return e
        finally:
            return 0

    def find(self):
        temp = Book.query.filter_by(name=self.name).first()
        return temp

    #根据Id查找
    @staticmethod
    def find_by_id(sid):
        temp = Book.query.filter_by(id=sid).first()
        return temp

    @staticmethod
    def get_count():
        return Book.query.count()

    def add_series(self):
        if self.seriesid == 0:
            return
        series = Series.find_by_id(self.seriesid)
        if series != None:
            self.series = series.name


    @staticmethod
    def get_book_range(start,pagesize):
        query = Book.query.order_by('id').offset(start).limit(pagesize)
        retlist = []
        for b in query:
            b.add_series()
            retlist.append(b._2dict())
        return retlist;



    def _2dict(self):
        d = {}
        d.update(self.__dict__)
        d.pop("_sa_instance_state")
        return d

class Pic(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    ossname = db.Column(db.String(128))
    bookid = db.Column(db.Integer,db.ForeignKey('book.id'))
    page = db.Column(db.Integer)
    def __init__(self,ossname,page):
        self.ossname = ossname
        self.page = page

    def add(self):
        try:
            db.session.add(self)
            db.session.commit()
            return self.id
        except Exception,e:
            db.session.rollback()
            return e
        finally:
            return 0

    @staticmethod
    def get_pages(id):
        ret = [];
        pages = Pic.query.filter_by(bookid = id).order_by("page")
        for page in pages:
            ret.append(page.ossname)
        return ret;

    def _2dict(self):
        d = {}
        # d['__class__'] = self.__class__.__name__
        # d['__module__'] = self.__module__
        d.update(self.__dict__)
        d.pop("_sa_instance_state")
        d.pop("bookid")
        d.pop("id")
        return d

    def find(self):
        temp = Pic.query.filter_by(bookid = self.bookid,page = self.page).first()
        return temp

class Series(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(64),unique=True)
    desc = db.Column(db.String(128))
    # books = db.relationship('Book',backref="series", lazy='dynamic')
    def __init__(self,name):
        self.name = name

    def _2dict(self):
        d ={}
        d.update(self.__dict__)
        d.pop("_sa_instance_state")
        return d
    #增加一条记录
    def add(self):
        try:
            db.session.add(self)
            db.session.commit()
            return self.id
        except Exception,e:
            db.session.rollback()
            return e
        finally:
            return 0

    #根据名称查找
    @staticmethod
    def find_by_name(aname):
        temp = Series.query.filter_by(name = aname).first()
        return temp

    #根据Id查找
    @staticmethod
    def find_by_id(sid):
        temp = Series.query.filter_by(id = sid).first();
        return temp

    @staticmethod
    def get_all():
        query = Series.query.order_by('id').offset(0)
        retlist = []
        for b in query:
            retlist.append(b._2dict())

        return retlist


if __name__ == "__main__":

    print Series.find_by_id(1)._2dict()

    # query = Book.query.order_by('id').offset(0)
    # retlist = []
    # for b in query:
    #     b.series = ''
    #     if b.seriesid != 0:
    #         series = Series.query.filter_by(id = b.seriesid).first()
    #         if series:
    #             b.series = series.name
    #
    #     # b.pagenum = 0
    #     # pages = Pic.query.filter_by(bookid = b.id).order_by("page")
    #     # for page in pages:
    #     #     b.pagenum = b.pagenum +1
    #     #
    #     # db.session.commit()
    #     print b.name,b.pagenum,b.series
    #
    #     # retlist.append(b._2dict())
